package com.punch.punchtable.service.impl;

import com.punch.punchtable.entity.Sumup;
import com.punch.punchtable.repository.SumupRepository;
import com.punch.punchtable.service.SumupService;
import com.punch.punchtable.vo.DepartmentVO;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.List;

@Service
public class SumupServiceImpl implements SumupService {
    @Autowired
    private SumupRepository repository;

    @Override
    public Page<Sumup> findByPage(Example<Sumup> example, Pageable pageable) {
        return repository.findAll(example, pageable);
    }


    @Override
    public List<String> findDept() {
        return repository.findAllDept();
    }

    @Override
    public List<Sumup> findAll(Example<Sumup> example) {
        return repository.findAll(example);
    }

    //    @Override
////    @Scheduled(cron = "0 0 1 * * ?")
//    @Scheduled(cron="0/3 * * * * ?")
//    public String findAllByRole1( ) {
////        repository.findAllByRole1();
//        return null;
//    }
    @PersistenceContext
    EntityManager entityManager;

    @Override
    public List<DepartmentVO> departmentList(Pageable pageable, String tmpSql) {
        String sql = "SELECT year,month,dept,Count(*) as peopleNum,sum(userearly) as totalUserEarly,sum(userlate) as totalUserLate FROM sumup " + tmpSql + " GROUP BY dept,year,month limit " +
                +pageable.getPageNumber() * pageable.getPageSize() + "," + pageable.getPageSize() + " ;";
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(DepartmentVO.class));
        return (List<DepartmentVO>) query.getResultList();
    }

    @Override
    public Long departmentListCount(String tmpSql) {
        String sql = "SELECT year,month,dept,Count(*) as peopleNum,sum(userearly) as totalUserEarly,sum(userlate) as totalUserLate FROM sumup " + tmpSql + " GROUP BY dept,year,month ;";
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(DepartmentVO.class));
        return (long) query.getResultList().size();
    }

}
